﻿using System.Text;
using Matrix.Common.DBUtility;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Matrix.Common.Utility;

namespace Matrix.MaKaYunShang.DAL
{
    public class BaseDAL<TO> where TO : BaseDAL<TO>, new()
    {
        private static readonly TO _instance = new TO();

        /// <summary>
        /// 返回当前业务逻辑实例
        /// </summary>
        /// <returns>实例</returns>
        public static TO Instance
        {
            get
            {
                return _instance;
            }
        }

        public SqlParameter[] GetListParam(string tableName, string columnName, string orderColumn, string groupColumn, int pageSize, int pageIndex, string strCondition)
        {
            SqlParameter[] param =
            {
                new SqlParameter("@Table",tableName), 
                new SqlParameter("@Column",columnName), 
                new SqlParameter("@OrderColumn",orderColumn), 
                new SqlParameter("@GroupColumn",groupColumn), 
                new SqlParameter("@PageSize",pageSize), 
                new SqlParameter("@CurrentPage",pageIndex), 
                new SqlParameter("@Group",0), 
                new SqlParameter("@Condition",strCondition), 
                new SqlParameter("@TotalCount",0), 
                new SqlParameter("@TotalPage",0)
            };
            return param;
        }

        protected List<T> GetQueryListPage<T>(string sql, SqlParameter[] parameters)
        {
            return GetQueryListPage<T>(sql, parameters, null);
        }

        protected List<T> GetQueryListPage<T>(string sql, SqlParameter[] parameters, SqlOptionContent sqlOptContent)
        {
            if (!string.IsNullOrWhiteSpace(sqlOptContent.OptionCondition))
                sql = sql.Replace(SqlOptionContent.OptionConditionTag,
                    string.IsNullOrWhiteSpace(sqlOptContent.OptionCondition) ? string.Empty : sqlOptContent.OptionCondition);

            if (!string.IsNullOrWhiteSpace(sqlOptContent.OptionGroupBy))
                sql = sql.Replace(SqlOptionContent.OptionGroupByTag,
                    string.IsNullOrWhiteSpace(sqlOptContent.OptionGroupBy) ? string.Empty : sqlOptContent.OptionGroupBy);

            if (!string.IsNullOrWhiteSpace(sqlOptContent.OptionJoin))
                sql = sql.Replace(SqlOptionContent.OptionJoinTag,
                    string.IsNullOrWhiteSpace(sqlOptContent.OptionJoin) ? string.Empty : sqlOptContent.OptionJoin);

            if (!string.IsNullOrWhiteSpace(sqlOptContent.OptionOrderBy))
                sql = sql.Replace(SqlOptionContent.OptionOrderByTag,
                    string.IsNullOrWhiteSpace(sqlOptContent.OptionOrderBy) ? string.Empty : sqlOptContent.OptionOrderBy);

            if (!string.IsNullOrWhiteSpace(sqlOptContent.OptionSelect))
                sql = sql.Replace(SqlOptionContent.OptionSelectTag,
                    string.IsNullOrWhiteSpace(sqlOptContent.OptionSelect) ? string.Empty : sqlOptContent.OptionSelect);

            var retList = DbHelperSQL.ExecuteSqlList<T>(sql, parameters);

            return retList.ToList();
        }

        /// <summary>
        /// 获取简单的单表分页
        /// </summary>
        /// <typeparam name="T">返回的Model类型</typeparam>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="tableName">表名</param>
        /// <param name="selectColumus">查询的列名（以逗号分割，并且首尾无逗号，如果传入null或者空，则默认查询*）</param>
        /// <param name="orderColumu">排序字段（例如单个：name desc   多个：name desc,age asc）</param>
        /// <param name="strWhere">查询条件（不带where）</param>
        /// <param name="totalCount">返回查询总条数</param>
        /// <param name="parameters">SQL参数（主要针对strWhere，没有则传null）</param>
        /// <returns>对象的List</returns>
        protected List<T> GetSimpleListPage<T>(int pageIndex, int pageSize, string tableName, string selectColumus, string selectColumus2, string orderColumu, string strWhere, ref int totalCount, SqlParameter[] parameters)
        {
            if (string.IsNullOrEmpty(selectColumus))
            {
                selectColumus = "*";
            }
            if (string.IsNullOrEmpty(selectColumus2))
            {
                selectColumus2 = selectColumus;
            }
            if (string.IsNullOrEmpty(strWhere))
            {
                strWhere = " 1=1 ";
            }
            string sql = @" select count(1) totalcount from " + tableName + " where " + strWhere + @";
                            select " + selectColumus + @" from 
                            (
                                select " + selectColumus2 + ",ROW_NUMBER() OVER(ORDER BY " + orderColumu + ") AS RowRank from " + tableName + " where " + strWhere + @"
                            ) as temp where RowRank between " + ((pageIndex - 1) * pageSize + 1) + " and " + pageIndex * pageSize;
            DataSet ds = new DataSet();
            if (parameters != null && parameters.Length > 0)
            {
                ds = DbHelperSQL.Query(sql, parameters);
            }
            else
            {
                ds = DbHelperSQL.Query(sql);
            }
            totalCount = int.Parse(ds.Tables[0].Rows[0]["totalcount"].ToString());
            return (List<T>)ServicesHelper.DataSetToIList<T>(ds, 1);
        }

        /// <summary>
        /// 获取简单的单表分页
        /// </summary>
        /// <typeparam name="T">返回的Model类型</typeparam>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="tableName">表名</param>
        /// <param name="selectColumus">查询的列名（以逗号分割，并且首尾无逗号，如果传入null或者空，则默认查询*）</param>
        /// <param name="orderColumu">排序字段（例如单个：name desc   多个：name desc,age asc）</param>
        /// <param name="strWhere">查询条件（不带where）</param>
        /// <param name="totalCount">返回查询总条数</param>
        /// <returns>对象的List</returns>
        protected List<T> GetSimpleListPage<T>(int pageIndex, int pageSize, string tableName, string selectColumus,string selectColumus2, string orderColumu, string strWhere, ref int totalCount)
        {
            return GetSimpleListPage<T>(pageIndex, pageSize, tableName, selectColumus, selectColumus2, orderColumu, strWhere, ref totalCount, null);
        }
        protected List<T> GetSimpleListPageExpand<T>(int pageIndex, int pageSize, string tableName, string selectColumus, string orderColumu, string strWhere, ref int totalCount)
        {
            return GetSimpleListPageExpand<T>(pageIndex, pageSize, tableName, selectColumus, orderColumu, strWhere, ref totalCount, null);
        }


        /// <summary>
        /// 获取简单的多表分页
        /// </summary>
        /// <typeparam name="T">返回的Model类型</typeparam>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页条数</param>
        /// <param name="tableName">表名</param>
        /// <param name="selectColumus">查询的列名（以逗号分割，并且首尾无逗号，如果传入null或者空，则默认查询*）</param>
        /// <param name="orderColumu">排序字段（例如单个：name desc   多个：name desc,age asc）</param>
        /// <param name="strWhere">查询条件（不带where）</param>
        /// <param name="totalCount">返回查询总条数</param>
        /// <param name="parameters">SQL参数（主要针对strWhere，没有则传null）</param>
        /// <returns>对象的List</returns>
        protected List<T> GetSimpleMultiListPage<T>(int pageIndex, int pageSize, List<string> tableNames, string selectColumus, string orderColumu, string strWhere, ref int totalCount, SqlParameter[] parameters)
        {
            if (string.IsNullOrEmpty(selectColumus))
            {
                selectColumus = "*";
            }
            if (string.IsNullOrEmpty(strWhere))
            {
                strWhere = " 1=1 ";
            }
            var sbSql = new StringBuilder();
            sbSql.Append("declare @total int;");
            sbSql.Append("set @total=0;");
            for (int i = 0; i < tableNames.Count; i++)
            {
                sbSql.Append("set @total += (select COUNT(1) from " + tableNames[i] + " where " + strWhere + ");");
            }
            sbSql.Append("select @total totalcount;");
            sbSql.Append("select " + selectColumus + " from");
            sbSql.Append("(");
            sbSql.Append("select " + selectColumus + ",ROW_NUMBER() OVER(ORDER BY " + orderColumu + ") AS RowRank from ");
            sbSql.Append("(");
            for (int i = 0; i < tableNames.Count; i++)
            {
                sbSql.Append("select " + selectColumus + " from " + tableNames[i] + " where " + strWhere + " ");
                if (i != tableNames.Count - 1)
                {
                    sbSql.Append(" union all ");
                }
            }
            sbSql.Append(") as temp ");
            sbSql.Append(") as tempTop where RowRank between " + ((pageIndex - 1) * pageSize + 1) + " and " + pageIndex * pageSize);
            DataSet ds = new DataSet();
            if (parameters != null)
            {
                ds = DbHelperSQL.Query(sbSql.ToString(), parameters);
            }
            else
            {
                ds = DbHelperSQL.Query(sbSql.ToString());
            }
            totalCount = int.Parse(ds.Tables[0].Rows[0]["totalcount"].ToString());
            return (List<T>)ServicesHelper.DataSetToIList<T>(ds, 1);
        }

        /// <summary>
        /// 获取数据库中包含某关键字的所有表名
        /// </summary>
        /// <param name="keyWord">表名关键字</param>
        /// <returns>所有表名组成的字符串（以“,”分割）</returns>
        public string GetAllLikeTableName(string keyWord)
        {
            var result = new StringBuilder();
            var ds = DbHelperSQL.Query("SELECT Name FROM SysObjects Where XType='U' and Name like '" + keyWord + "%' order by Name");
            if (ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    result.Append(ds.Tables[0].Rows[i]["Name"] + ",");
                }
            }
            return result.ToString().Trim(',');
        }

        protected List<T> GetSimpleListPageExpand<T>(int pageIndex, int pageSize, string tableName, string selectColumus, string orderColumu, string strWhere, ref int totalCount, SqlParameter[] parameters)
        {
            if (string.IsNullOrEmpty(selectColumus))
            {
                selectColumus = "*";
            }
            if (string.IsNullOrEmpty(strWhere))
            {
                strWhere = " 1=1 ";
            }
            string sql = @" select count(1) totalcount from " + tableName + " where " + strWhere + @";
                            select * from 
                            (
                                select " + selectColumus + ",ROW_NUMBER() OVER(ORDER BY " + orderColumu + ") AS RowRank from " + tableName + " where " + strWhere + @"
                            ) as temp where RowRank between " + ((pageIndex - 1) * pageSize + 1) + " and " + pageIndex * pageSize;
            DataSet ds = new DataSet();
            if (parameters != null)
            {
                ds = DbHelperSQL.Query(sql, parameters);
            }
            else
            {
                ds = DbHelperSQL.Query(sql);
            }
            totalCount = int.Parse(ds.Tables[0].Rows[0]["totalcount"].ToString());
            return (List<T>)ServicesHelper.DataSetToIList<T>(ds, 1);
        }
    }
}
